{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "381c71ec-741f-417a-bf07-f34e3785170e",
   "metadata": {},
   "source": [
    "Chapter 22\n",
    "# 利用merge() 完成左侧独有、右侧独有、全外独有\n",
    "Book_1《编程不难》 | 鸢尾花书：从加减乘除到机器学习  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e674549c-e3e6-489e-852a-904edc55d27c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ce10a357-5c46-40ee-9404-db0f8034f1eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>M</th>\n",
       "      <th>X</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M  X\n",
       "0  1  a\n",
       "1  2  b\n",
       "2  3  c"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建两个数据帧\n",
    "left_data = {\n",
    "    'M': [ 1,   2,   3],\n",
    "    'X': ['a', 'b', 'c']}\n",
    "left_df = pd.DataFrame(left_data)\n",
    "left_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0447d30b-af45-4c1c-8d37-d65877a1ecf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   X   N\n",
       "0  b  22\n",
       "1  c  33\n",
       "2  d  44"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_data = {\n",
    "    'X': ['b', 'c', 'd'],\n",
    "    'N': [ 22,  33,  44]}\n",
    "right_df = pd.DataFrame(right_data)\n",
    "right_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b0e8af9e-372c-42ce-ba15-eae6257193c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# LEFT EXCLUSIVE\n",
    "left_exl = left_df.merge(right_df, \n",
    "                         on='X', \n",
    "                         how='left', \n",
    "                         indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "972e87d9-33ca-4e0f-845a-e3630e1ad7dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>M</th>\n",
       "      <th>X</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M  X   N\n",
       "0  1  a NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_exl = left_exl[\n",
    "    left_exl['_merge'] == 'left_only'].drop(\n",
    "    columns=['_merge'])\n",
    "left_exl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4b06fdd6-8db0-4c04-875e-a08ddd79b226",
   "metadata": {},
   "outputs": [],
   "source": [
    "# RIGHT EXCLUSIVE\n",
    "right_exl = left_df.merge(right_df, \n",
    "                          on='X', \n",
    "                          how='right', \n",
    "                          indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b2ff7d47-2e6f-4581-b926-0607e5037089",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>M</th>\n",
       "      <th>X</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>d</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    M  X   N\n",
       "2 NaN  d  44"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_exl = right_exl[\n",
    "    right_exl['_merge'] == 'right_only'].drop(\n",
    "    columns=['_merge'])\n",
    "right_exl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8f76e5b8-e016-4eb3-aafb-a71bb3750544",
   "metadata": {},
   "outputs": [],
   "source": [
    "# FULL OUTER EXCLUSIVE\n",
    "outer_exl = left_df.merge(right_df, \n",
    "                          on='X', \n",
    "                          how='outer', \n",
    "                          indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5a19b431-bb17-4d2f-8c51-174c22980c43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>M</th>\n",
       "      <th>X</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>d</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     M  X     N\n",
       "0  1.0  a   NaN\n",
       "3  NaN  d  44.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outer_exl = outer_exl[\n",
    "    outer_exl['_merge'] != 'both'].drop(\n",
    "    columns=['_merge'])\n",
    "outer_exl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c7638ef-cec4-47a8-990c-0063755135c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2d1e15f-c0a6-4ca9-b9df-749b2a6deedd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
